package examination;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
 * 考试类，提供考试相关操作方法
 * @author ChangingSelf
 * @author EndlessPeak
 */
public class ExamBean {
	private static DatabaseBean databaseBean=new DatabaseBean();
	
	private String examNo="";//考试编号
	private String status="";//考试状态
	private String term="";//学期号
	private String college="";//学院号
	private String collegeName = "";//学院名称
	private String courseNo="";//课程号
	private String courseName="";//课程名，用于数据表格展示
	private String[] classNo;//参加考试的班级号列表
	private Date date = new Date();//考试日期
	private String time="";//考试时间，未格式化文本
	private String location="";//考试地点
	private InvigilatorBean[] invigilator;//监考老师，数组第一个老师为主监考老师
	private int workload;//监考工分(等效于监考工作量)
	private int need;//监考缺少人数
	private String applierNo = "";//申请者编号
	private Date applyDate = new Date();//申请日期
	private String comment="";//备注
	private String refuseReason="";//审核不通过的理由
	
	
	
	public String getExamNo() {
		return examNo;
	}
	public void setExamNo(String examNo) {
		this.examNo = examNo;
	}
	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	public String getTerm() {
		return term;
	}
	public void setTerm(String term) {
		this.term = term;
	}
	public String getCollege() {
		return college;
	}
	public void setCollege(String college) {
		this.college = college;
	}
	public String getCourseName() {
		return courseName;
	}
	public void setCourseName(String courseName) {
		this.courseName = courseName;
	}
	public String[] getClassNo() {
		return classNo;
	}
	public void setClassNo(String[] classNo) {
		this.classNo = classNo;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public String getTime() {
		return time;
	}
	public void setTime(String time) {
		this.time = time;
	}
	public String getLocation() {
		return location;
	}
	public void setLocation(String location) {
		this.location = location;
	}
	public InvigilatorBean[] getInvigilator() {
		return invigilator;
	}
	public void setInvigilator(InvigilatorBean[] invigilator) {
		this.invigilator = invigilator;
	}
	public int getWorkload() {
		return workload;
	}
	public void setWorkload(int workload) {
		this.workload = workload;
	}
	public int getNeed() {
		return need;
	}
	public void setNeed(int need) {
		this.need = need;
	}
	public static DatabaseBean getDatabaseBean() {
		return databaseBean;
	}
	public static void setDatabaseBean(DatabaseBean databaseBean) {
		ExamBean.databaseBean = databaseBean;
	}
	public String getCourseNo() {
		return courseNo;
	}
	public void setCourseNo(String courseNo) {
		this.courseNo = courseNo;
	}

	public String getApplierNo() {
		return applierNo;
	}
	public void setApplierNo(String applierNo) {
		this.applierNo = applierNo;
	}
	public Date getApplyDate() {
		return applyDate;
	}
	public void setApplyDate(Date applyDate) {
		this.applyDate = applyDate;
	}
	public String getComment() {
		return comment;
	}
	public void setComment(String comment) {
		this.comment = comment;
	}
	public String getRefuseReason() {
		return refuseReason;
	}
	public void setRefuseReason(String refuseReason) {
		this.refuseReason = refuseReason;
	}
	public String getCollegeName() {
		return collegeName;
	}
	public void setCollegeName(String collegeName) {
		this.collegeName = collegeName;
	}
	/**
	 * 无参构造函数和带参构造函数
	 */
	public ExamBean() {
		super();
	}

	public ExamBean(String examNo, String status, String term, String college, String courseName, String[] classNo,
			Date date, String time, String location, InvigilatorBean[] invigilator, int workload, int need) {
		super();
		this.examNo = examNo;
		this.status = status;
		this.term = term;
		this.college = college;
		this.courseName = courseName;
		this.classNo = classNo;
		this.date = date;
		this.time = time;
		this.location = location;
		//this.invigilator = invigilator;
		this.workload = workload;
		this.need = need;
	}
	/**
	 * 添加考试信息
	 * @param exam
	 * @return 是否添加成功
	 */
	public static boolean addExam(ExamBean exam)//单个考试添加函数
	{
		SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");//用于日期格式化
		String date =sdf.format(exam.getDate());
		String applyDate = sdf.format(exam.getApplyDate());
		//添加exam表相关的属性
		String sql = String.format("insert into "
				+ "exam(exam_id,term_id,course_id,exam_wl,exam_date,location,period,teacherNeed,exam_status,`comment`,teacher_id,apply_date,refuse_reason)"
				+ "values('%s','%s','%s',%d,'%s','%s','%s',%d,'%s','%s','%s','%s','%s');"
				,exam.getExamNo()
				,exam.getTerm()
				,exam.getCourseNo()
				,exam.getWorkload()
				,date
				,exam.getLocation()
				,exam.getTime()
				,exam.getNeed()
				,exam.getStatus()
				,exam.getComment()
				,exam.getApplierNo()
				,applyDate
				,exam.getRefuseReason()
				);
		
		boolean success = databaseBean.executeUpdate(sql);
		if(!success) return false;
		
		//添加考试-班级表的记录
		String[] classNoList = exam.getClassNo();
		if(classNoList!=null) {
			for(String classNo :classNoList) {
				sql = String.format("insert into examclasses(exam_id,class_id)"
						+ "values('%s','%s');"
						, exam.getExamNo(),classNo);
				success = databaseBean.executeUpdate(sql);
				if(!success) return false;
			}
		}
		
		//添加监考记录
		InvigilatorBean[] invigilatorList = exam.getInvigilator();
		if(invigilatorList!=null) {
			boolean isMain = true;
			for(InvigilatorBean invigilator:invigilatorList) {
				success = InvigilatorBean.arrange(exam.getExamNo(), invigilator.getStaffNo(), isMain);
				isMain=false;
				if(!success) return false;
			}
		}
				
		return true;
	}
	
	public static boolean addExams(ExamBean[] exams) {
		for(ExamBean exam:exams)
			if(!addExam(exam))	return false;
		return true;
	}
	/**
	 * 修改考试信息
	 * @param exam
	 * @return 是否成功
	 */
	public static boolean updateExam(ExamBean exam) {
		try {
			SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");//用于日期格式化
			String date =sdf.format(exam.getDate());
			String applyDate= sdf.format(exam.getApplyDate());
			//修改exam表中的内容
			String sql = "UPDATE exam SET " + 
					"term_id=?," + //1
					"course_id=?," + //2
					"exam_wl=?," + //3
					"exam_date=?," + //4
					"location=?," + //5
					"period=?," + //6
					"teacherNeed=?," + //7
					"exam_status=?," + //8
					"`comment`=?," + //9
					"teacher_id=?," + //10
					"apply_date=?," + //11
					"refuse_reason=? " + //12
					"WHERE exam_id=?";//13

			Connection connection = databaseBean.getConn();
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, exam.getTerm());
			ps.setString(2, exam.getCourseNo());
			ps.setInt(3, exam.getWorkload());
			ps.setString(4, date);
			ps.setString(5, exam.getLocation());
			ps.setString(6, exam.getTime());
			ps.setInt(7, exam.getNeed());
			ps.setString(8, exam.getStatus());
			ps.setString(9, exam.getComment());
			ps.setString(10, exam.getApplierNo());
			ps.setString(11, applyDate);
			ps.setString(12, exam.getRefuseReason());
			ps.setString(13, exam.getExamNo());
			
			ps.executeUpdate();
			ps.close();
			
			//修改班级-考试对应关系
			//先删除原本的元组
			sql = "DELETE FROM examclasses WHERE exam_id=?;";
			ps = connection.prepareStatement(sql);
			ps.setString(1, exam.getExamNo());
			ps.executeUpdate();
			ps.close();
			//再将新的的内容插入进去
			String[] classNoList = exam.getClassNo();
			if(classNoList!=null) {
				for(String classNo :classNoList) {
					sql = String.format("insert into examclasses(exam_id,class_id)"
							+ "values('%s','%s');"
							, exam.getExamNo(),classNo);
					boolean success = databaseBean.executeUpdate(sql);
					if(!success) return false;
				}
			}
			
			//修改监考记录
			sql = "DELETE FROM proctor WHERE exam_id=?;";
			ps = connection.prepareStatement(sql);
			ps.setString(1, exam.getExamNo());
			ps.executeUpdate();
			ps.close();
			InvigilatorBean[] invigilatorList = exam.getInvigilator();
			if(invigilatorList!=null) {
				boolean isMain = true;
				for(InvigilatorBean invigilator:invigilatorList) {
					boolean success = InvigilatorBean.arrange(exam.getExamNo(), invigilator.getStaffNo(), isMain);
					isMain=false;
					if(!success) return false;
				}
			}
			
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 删除考试
	 * @param exam 要删除的考试，其中必须设置考试号属性
	 * @return 是否删除成功
	 */
	public static boolean delExam(ExamBean exam) {
		String sql = String.format("delete from exam where exam_id='%s'"
				,exam.getExamNo());
		return databaseBean.executeUpdate(sql);
	}
	
	/**
	 * 查询考试
	 * @param whereString 附加条件，注意开头要加连接关键字，例如“AND”
	 * @return 根据条件查询到的考试对象
	 */
	public static ExamBean[] query(String whereString) {
		if(whereString==null) return null;
		String sql = "SELECT DISTINCT exam_id,term_id,exam.course_id,exam_wl,exam_date,location,period,teacherNeed,exam_status,course_name,exam.teacher_id,apply_date,refuse_reason,comment FROM exam,course,college\r\n" + 
				"WHERE exam.course_id = course.course_id "+whereString;
		ResultSet rs = databaseBean.executeQuery(sql);
		
		try {
			rs.last();
			ExamBean exams[]=new ExamBean[rs.getRow()];
			rs.beforeFirst();
			int i=0;
			while(rs.next()) {
				ExamBean exam=new ExamBean();
				//获取可直接查询的单值属性
				exam.setExamNo(rs.getString("exam_id"));
				exam.setTerm(rs.getString("term_id"));
				exam.setWorkload(rs.getInt("exam_wl"));
				exam.setDate(rs.getDate("exam_date"));
				exam.setLocation(rs.getString("location"));
				exam.setTime(rs.getString("period"));
				exam.setStatus(rs.getString("exam_status"));
				exam.setCourseName(rs.getString("course_name"));
				exam.setCourseNo(rs.getString("course_id"));
				exam.setNeed(rs.getInt("teacherNeed"));
				exam.setApplierNo(rs.getString("teacher_id"));
				exam.setComment(rs.getString("comment"));
				exam.setApplyDate(rs.getDate("apply_date"));
				exam.setRefuseReason(rs.getString("refuse_reason"));
				exams[i]=exam;
				i++;
			}
			rs.close();
			//查询数组属性
			for(ExamBean exam : exams) {
				//班级列表和学院号，以最后一个班级的学院为准
				sql = String.format("SELECT class.class_id,class.coll_id,coll_name FROM examclasses,class,college WHERE exam_id='%s' AND class.class_id=examclasses.class_id AND class.coll_id=college.coll_id;", 
						exam.getExamNo());
				rs = databaseBean.executeQuery(sql);
				rs.last();
				String[] classNoList = new String[rs.getRow()];
				rs.beforeFirst();
				i=0;
				while(rs.next()) {
					classNoList[i] = rs.getString("class_id");
					exam.setCollege(rs.getString("coll_id"));
					exam.setCollegeName(rs.getString("coll_name"));
					i++;
				}
				exam.setClassNo(classNoList);
				
				rs.close();
				//查询该考试的监考老师列表
				//TODO 对应方法实现后替换此处
				
				String[] staffNoList = InvigilatorBean.queryByExamNo(exam.getExamNo());
				InvigilatorBean[] invigilatorBeans = InvigilatorBean.queryByNos(staffNoList);
				exam.setInvigilator(invigilatorBeans);
				//exam.setInvigilator(new InvigilatorBean[1]);
				
			}
			
			return exams;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 查询所有考试
	 * @return 所有考试的考试对象
	 */
	public static ExamBean[] queryAll() {
		return query("");
	}
	
	/**
	 * 根据考试编号查询考试
	 * @param examNo 考试编号
	 * @return 考试编号为examNo的考试对象
	 */
	public static ExamBean queryByNo(String examNo) {
		ExamBean[] exams = query(String.format("AND exam.exam_id='%s'", examNo));
		ExamBean exam = new ExamBean();
		if(exams!=null&&exams.length!=0){
			exam = exams[0];
		}
		return exam;
	}
	/**
	 * 根据考试状态查询考试
	 * @param status 考试状态
	 * @return 考试状态为status的考试对象
	 */
	public static ExamBean[] queryByStatus(String status) {
		return query(String.format("AND exam.exam_status='%s'", status));
	}
	/**
	 * 根据监考老师查询考试
	 * @param staffNo 监考老师的职工号
	 * @return 职工号为staffNo的老师所监考的考试
	 */
	public static ExamBean[] queryByInvigilator(String staffNo) {
		try {
			//查询职工号为staffNo的老师所监考的考试的考试编号列表
			String sql = String.format("SELECT * FROM proctor WHERE teacher_id='%s';", 
					staffNo);
			ResultSet rs = databaseBean.executeQuery(sql);
			rs.last();
			String examNoList[]=new String[rs.getRow()];
			rs.beforeFirst();
			int i=0;
			while(rs.next()) {
				examNoList[i] = rs.getString("exam_id");
				i++;
			}
			rs.close();	
			//获取考试编号列表所对应的考试对象列表
			List<ExamBean> examList = new ArrayList<ExamBean>();
			if(examNoList!=null) {
				for(String examNo:examNoList) {
					ExamBean tempExam =queryByNo(examNo);
					//将结果添加进结果列表
					examList.add(tempExam);
				}
			}
			
			return examList.toArray(new ExamBean[examList.size()]);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 根据传入的日期获取学期号
	 * @param date
	 * @return date对应的学期号，若找不到或者出错则返回null
	 */
	public static String getTermNo(Date date) {
		//查询
		String sql="SELECT * FROM term;";
		ResultSet rs  = databaseBean.executeQuery(sql);
		SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
		try {
			while(rs.next()) {
				String beginDateString = rs.getString("begin_date");
				String endDateString = rs.getString("end_date");
				Date beginDate = sdf.parse(beginDateString);
				Date endDate = sdf.parse(endDateString);
				if(date.after(beginDate) && date.before(endDate)) {
					//若该日期在本学期中，则返回学期号
					return rs.getString("term_id");
				}
			}
			return null;
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
				
	}
	
	public void print() {
		System.out.println("--------------------------------------------");
		System.out.println("考试编号："+examNo);
		System.out.println("考试状态:"+status);
		System.out.println("学期:"+term);
		System.out.println("学院:"+college);
		System.out.println("课程名:"+courseName);
		System.out.println("考试班级："+String.join(",", classNo));
		System.out.println("考试日期："+date);
		System.out.println("考试时间："+time);
		System.out.println("考试地点："+location);
		String invigilatorString = "";
		for(InvigilatorBean invigilator: this.invigilator) {
			//invigilatorString+=invigilator.getName()+",";
		}
		System.out.println("监考老师："+invigilatorString);
		System.out.println("监考工分："+workload);
		System.out.println("监考总需人数："+need);
		System.out.println("--------------------------------------------");
	}
	
	public static void main(String[] args) {
		Date date = new Date();
		String termNo = ExamBean.getTermNo(date);
		System.out.println(termNo);
		
		
		
		
		/*
		ExamBean[] examBeans = ExamBean.queryAll();
		//ExamBean[] examBeans = ExamBean.queryByNo("10001");
		//ExamBean[] examBeans = ExamBean.queryByInvigilator("10001");
		
		if(examBeans!=null && examBeans.length!=0) {
			for(ExamBean exam:examBeans) {
				exam.print();
			}
		}else {
			System.out.println("找不到");
		}
		ExamBean testExam =new ExamBean();
		testExam.setExamNo("E10006");
		testExam.setDate(new Date());
		testExam.setTerm("2019-2020-2");
		testExam.setCourseNo("C10001");
		testExam.setWorkload(1);
		testExam.setLocation("WH1403");
		testExam.setTime("下午7,8节");
		testExam.setNeed(4);
		testExam.setStatus("待审核");
		testExam.setApplierNo("T10001");
		testExam.setComment("");
		//testExam.setRefuseReason(null);
		testExam.setApplyDate(new Date());
		
		String[] classNoList = new String[] {
			"2017240203","2017240202","2017240201"
		};
		testExam.setClassNo(classNoList);
		
		ExamBean.updateExam(testExam);
		examBeans = ExamBean.queryAll();
		if(examBeans!=null && examBeans.length!=0) {
			for(ExamBean exam:examBeans) {
				exam.print();
			}
		}else {
			System.out.println("找不到");
		}
		*/
	}
	
}
